from re import findall
import pymysql
from .getConfig import getConfig, logger


class JetMySQL:
    def __init__(self):
        self.conn = self.__connectMysql()
        self.cursor = self.conn.cursor()

    @staticmethod
    def __connectMysql():
        mysql_info = {}
        cfg = getConfig()
        mysql_info['host'] = cfg.get('mysql', 'host')
        mysql_info['port'] = cfg.getint('mysql', 'port')
        mysql_info['user'] = cfg.get('mysql', 'user')
        mysql_info['password'] = cfg.get('mysql', 'password')
        mysql_info['db'] = cfg.get('mysql', 'db_name')
        mysql_info['charset'] = 'utf8'

        return pymysql.connect(**mysql_info,
                               cursorclass=pymysql.cursors.DictCursor)

    def insert(self, items, info="item"):
        if info == "item":
            insert_cmd = "replace into productList(companyName, category, category2, productName, " \
                "productUrl, productPrice, productImageUrl, productImageName"\
                ", productMore) VALUES (%s,%s,%s, %s, %s,%s,%s,%s, %s)"
            data = (items.get("companyName"), items.get("category"),
                    items.get("category2", ""), items.get("productName"),
                    items.get("productUrl"), items.get(
                        "productPrice"), items.get("productImageUrl"),
                    items.get("productImageName"), str(items.get("productMore", "")))

        elif info == "product":
            insert_cmd = "replace into productCompany(companyName, category, category2, " \
                "url) VALUES (%s,%s,%s,%s)"
            data = (items.get("companyName"), items.get("category"),
                    items.get("category2", ""), items.get("url"))
        else:
            logger.error("not supported !!")
            assert False

        try:
            self.cursor.execute(insert_cmd, data)
            self.conn.commit()
        except Exception as e:
            print(e)
            print(items)
            self.conn.rollback()

    def executor(self, sql_cmd):
        try:
            self.cursor.execute(sql_cmd)
            self.conn.commit()
            return self.cursor.fetchall()
        except Exception as e:
            print(e)
            self.conn.rollback()

    def db_close(self):
        self.conn.close()


if __name__ == "__main__":
    import time
    mysql = JetMySQL()

    bb = mysql.executor("select companyName, category, productUrl, productImageUrl, productImageName from productList    where companyName='joann' and category='spring-decor' and \
                       productImageStatus='no'")
    print(len(bb))
